import pymysql


class Dict:
    def __init__(self):
        self.args = {
            "host": "localhost",
            "port": 3306,
            "user": "root",
            "database": "dict",
            "password": "123456",
            "charset": "utf8"
        }
        self.connect()

    def connect(self):
        self.db = pymysql.connect(**self.args)

    def close(self):
        self.db.close()

    def cursor(self):
        self.cur = self.db.cursor()

    def register(self, name, password):
        sql = "insert into user (name,password) values (%s,%s);"
        try:
            self.cur.execute(sql, [name, password])
            self.db.commit()
            return True
        except:
            self.db.rollback()
            return False

    def login(self, name, password):
        sql = "select name from user where name=%s and password=%s;"
        self.cur.execute(sql, [name, password])
        if self.cur.fetchone():
            return True
        else:
            return False

    def query(self, word, name):
        sql = "select mean from words where word=%s;"
        self.cur.execute(sql, [word, ])
        mean = self.cur.fetchone()
        print(mean)
        if mean:
            sql = "select id from user where name=%s;"
            self.cur.execute(sql, [name, ])
            user_id = self.cur.fetchone()[0]

            sql = 'select id from words where word=%s'
            self.cur.execute(sql, [word, ])
            words_id = self.cur.fetchone()[0]

            try:
                sql = 'insert into history (user_id,words_id) values (%s,%s);'
                self.cur.execute(sql, [user_id, words_id])
                self.db.commit()
            except:
                self.db.rollback()

            return mean[0]
        else:
            return "NOT FOUND"

    def history(self, name):
        sql = "select name,word,time from user " \
              "left join history on user.id=history.user_id " \
              "left join  words on words.id=history.words_id where name=%s order by time desc limit 10;"
        self.cur.execute(sql, [name, ])
        record = ''
        for item in self.cur.fetchall():
            record += str(item) + "\n"
        return record
